/*
 * this is the new final version of the csvdb file .
 * I have made some changes in the column file as well.
 * I added the attribute to column structure called relatioName, and set it in scanfile method.
 * I have added the functionality to do join oon conditions R.B=S.D.
 * I also checked in the condition if they are given in reverse order, i.e.
 * it will work in both these conditions.
 * U=Join(R,S,R.B=S.D) and
 *  U=Join(R,S,S.D=R.B).

 */
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.util.regex.*;
import java.io.*;
import java.nio.*;
import java.util.*;

public class csvdb {
	static String inputfile,outputfile,method,arg;
	static String outputRelation ="";
	static String inputRelation ="";
	
	public static void main(String[] args) {
		String[] cols = new String[20];
		if(args.length<1)
			System.out.print("Arguments Missing!!!\n. Please provide some query to process.");
		else 
			{
				if(args.length>1)
				System.out.print("More Than Required Arguments. Check Syntax.!!!\n");
			    int cmdType = findCmd(args[0]);
				if(cmdType==0)
					System.out.print("Error in Input Command. please check again!!!");
				if(cmdType==1){
					extractDisplayComponents(args[0]);
				    column R = scanfile(outputRelation);
				    for (int i =0; i<R.sizeofcolumn();i++)
				    {
				    	System.out.printf("%14s", R.getcolumn(i).getname());
				    }
				    System.out.print("\n");
				    for(int row=0;row<R.sizeofchild();row++)
				    {
				    	for (int colnum=0; colnum<R.sizeofcolumn();colnum++ ){
				    		String temp = R.getcolumn(colnum).getchild(row);
				    		if (temp!=null&&temp.equals("")){
				    			System.out.printf("      NULL");
				    		}else{
				    			temp = R.getcolumn(colnum).getchild(row);
				    			if (temp.length()>=12){
				    				temp = temp.substring(0, 12);
				    			}
				    			System.out.printf("%14s", temp);
				    		}				    		
				    	}
				    	System.out.print("\n");
				    }
				    
				}
					
				if(cmdType==2)
					extractSelectComponents(args[0]);
				if(cmdType==4)
				{
				extractJoinComponents(args[0]);
				}
				if(cmdType==5)
				{
				System.out.print("This is a sum command\n");
				extractSum(args[0]);
				}
				if (cmdType == 6)
				{
					System.out.print("This is a sort command\n");
					extractSort(args[0]);
				}
				if (cmdType == 7){
					System.out.print("This is a index command\n");
					extractIndex(args[0]);
					
				}
				if(cmdType==3){
					cols = extractProjectComponents(args[0]);
					column R = scanfile(inputRelation);
					//column S = new column();
					BufferedWriter out = openforwrite(outputRelation);
				    int i=0;
				    while(cols[i]!=null){
				    	i++;
				    }
				    try{
					    if (i>=1){
					    	int[] index = new int[i];
					    	for(int k=0; k<i; k++){
					    		index[k] = R.indexofcolumn(cols[k]);
					    		if (index[k] ==-1){
							    	System.out.print("No column named "+cols[k]+" for projecting, Exit!");
							    	System.exit(1);
					    		}				    		
					    	}//end for
					    	//S.setname(cols[0]);
					    	out.write(cols[0]);
					    	for (int k=1; k<i; k++){
					    		//S.addsibling(cols[k]);
					    		out.write(","+cols[k]);
					    	}
					    	out.write("\n");				    	
					    	for (int k=0; k<R.sizeofchild();k++){
					    		if(!R.existuple(k, index)){
					    			out.write(R.getcolumn(index[0]).getchild(k));
						    		for (int j=1; j<i; j++){
							    		out.write(","+R.getcolumn(index[j]).getchild(k));
							    	}
						    		out.write("\n"); 
					    		}
					    	}
					    	out.close();
					    	
					    }else{
					    	System.out.print("No column to Project, Exit!");
					    	System.exit(1);
					    }
				    }catch (IOException ioe)
					  {
						  System.err.println(ioe);
					  }
					
				}//end if(cmdType==3)				
			}
		System.exit(0);
	}
	
	private static int findCmd(String cmd)
	{
		
		int returnVal=0;
		String token="";
		String regex = "\\s*[Dd][Ii][Ss][Pp][Ll][Aa][Yy]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\)\\s*";
		//String regex1="\\s*([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\s*\\=\\s*[Ss][Ee][Ll][Ee][Cc][Tt]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,)+\\w+\\)";
		String regex1="\\s*([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\s*\\=\\s*[Ss][Ee][Ll][Ee][Cc][Tt]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,\\w+\\)";
		String regex2="\\s*\\w+\\s*\\=\\s*[Pp][Rr][Oo][Jj][Ee][Cc][Tt]\\s*\\(";
		String regex3 = "\\s*\\w+\\s*\\=\\s*[Jj][Oo][Ii][Nn]\\s*\\(";
		String regex4 = "\\s*([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\s*\\=\\s*[Ss][Uu][Mm]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,\\s*|([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\),([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\)";
		String regex5 = "\\s*[Ss][Oo][Rr][Tt]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,\\s*([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\)";
		String regex6 = "\\s*[Ii][Nn][Dd][Ee][Xx]\\s*\\(([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*,\\s*([A-Z]|[a-z])+[0-9]*([A-Z]|[a-z])*\\)";
		Pattern p =Pattern.compile(regex);
		Matcher m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 1;
		}
		p =Pattern.compile(regex1);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 2;
		}
		p =Pattern.compile(regex2);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 3;
		}
		p =Pattern.compile(regex3);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 4;
		}
		p =Pattern.compile(regex4);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 5;
		}
		p =Pattern.compile(regex5);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 6;
		}
		p =Pattern.compile(regex6);
		m = p.matcher(cmd); 
		if (m.find()) 
	      {
	    	token=m.group();
	    	returnVal= 7;
		}
	return returnVal;
	}
	private static void extractDisplayComponents(String cmd)
	{
		boolean flag = false;
		outputRelation ="";
		int index=0;
		char[] output = new char[50];
		
		//find components for display statements.
		for(int i=0;i<cmd.length();i++)
		  {
				if(flag)
			    {
				  if(cmd.charAt(i)==')')
					break;
				  else
					{
					  output[index]=cmd.charAt(i);
					  System.out.print("output["+index+"] :  "+output[index]+"\n");
					  index++;
					}
			    }
				if(cmd.charAt(i)=='(')
				flag=true;
		    
		}
			outputRelation = new String (output);
			outputRelation = outputRelation.toLowerCase().trim();
	        
	}

	private static void extractSelectComponents(String cmd)
	{
		boolean flag = false;
		boolean attrStart = false;
		boolean inputdetected=false;
		String outputRelation ="";
		String inputRelation ="";
		String condition ="";
		int index=0, index2=0, cond_index=0;
		char[] output = new char[50];
		char[] input = new char[50];
		char[] cond = new char[50];
		
		
		//find components for select statements.
		for(int i=0;i<cmd.length();i++)
		  {
				if(cmd.charAt(i)=='='&& !flag)
					{
					flag=true;
					outputRelation = new String (output); 
					if(outputRelation.charAt(0)=='"')
						outputRelation = (outputRelation.substring(1,outputRelation.length())).trim();
						outputRelation = outputRelation.toLowerCase();}
				else 
					{
					output[index]=cmd.charAt(i);
					index++;
					}
				if(attrStart)
				{
					if(!inputdetected)
					{
						if(cmd.charAt(i)==',')
						{
							index2=0;
							inputRelation = new String (input);
							inputRelation= inputRelation.toLowerCase().trim();
							
						inputdetected=true;
						}
						else
							{
							input[index2]=cmd.charAt(i);
							index2++;
							}
					}
					else
						if(cmd.charAt(i)==')')
						{
							condition = new String (cond);
						}
						else
							{
							cond[cond_index]=cmd.charAt(i);
							cond_index++;
							}
					}
				if(cmd.charAt(i)=='('&&flag)
					{
					attrStart=true;
					//index=0;
					}
				if(cmd.charAt(i)==')'&&attrStart)
					break;
				
					
				}
		column res = scanfile(inputRelation.trim());
		select(res,outputRelation.trim(),condition);
		  }
	private static void extractJoinComponents(String cmd)
	{
		boolean flag = false;
		boolean attrStart = false;
		String outputRelation ="";
		String inputRelation1 ="";
		String inputRelation2 ="";
		String joinCol="";
		int index=0;
		boolean relationOneFound=false;
		char[] output = new char[50];
		//find components for Join statements.
		for(int i=0;i<cmd.length();i++)
		  {
				if(cmd.charAt(i)=='='&& !flag)
					{
					flag=true;
					outputRelation = new String (output); 
					if(outputRelation.charAt(0)=='"')
						outputRelation = (outputRelation.substring(1,outputRelation.length())).trim();
						outputRelation = outputRelation.toLowerCase().trim();}
				else {
					if(!flag)
				     {
					output[index]=cmd.charAt(i);
					index++;
					}
					}
				if(attrStart)
				{
					String regex = "\\s*\\w+\\s*\\,";
					Pattern p =Pattern.compile(regex);
					Matcher m = p.matcher(cmd); 
					while (m.find()) 
				      {
				    	
						if(relationOneFound)
							inputRelation2=m.group().substring(0,m.group().length()-1);
				    	else {
				    		inputRelation1=m.group().substring(0,m.group().length()-1);
				    		relationOneFound=true;
						}
				    	
					}
					regex = "\\s*\\w+\\s*\\)|\\s*\\w+\\s*=\\s*\\w+\\s*\\)|\\s*\\w+\\.\\w+\\s*=\\s*\\w+\\.\\w+\\s*\\)";
					p =Pattern.compile(regex);
					m = p.matcher(cmd); 
					if (m.find()) 
				      {
				    	joinCol=m.group().substring(0,m.group().length()-1);
				      }
				}	
				if(cmd.charAt(i)=='('&&flag)
					{
					attrStart=true;
					
					}
				if(cmd.charAt(i)==')'&&attrStart)
					{
					
					break;
					}
				
					
				}
		column inputRel1 = scanfile(inputRelation1.trim());
		column inputRel2 = scanfile(inputRelation2.trim());
		if(joinCol.indexOf("=")<0){
			join(inputRel1, inputRel2, outputRelation, joinCol);	
		}else{
			EquiJoin(inputRel1, inputRel2, outputRelation, joinCol);
		}
		
		 }
	
	private static String[] extractProjectComponents(String cmd)
	{
		boolean flag = false;
		boolean attrStart = false;
		boolean colStart=false;
		
		String[] cols = new String[100];
		int index=0, cond_index=0;
		char[] output = new char[50];
		for(int i=0;i<cmd.length();i++)
		  {
			
				if(cmd.charAt(i)=='=')
					{
					flag=true;
					outputRelation = new String (output);
					if(outputRelation.charAt(0)=='"')
						outputRelation = (outputRelation.substring(1,outputRelation.length())).trim();
					outputRelation = outputRelation.toLowerCase();
			        }
				else 
					{
					if(!flag)
					{
					output[index]=cmd.charAt(i);
					index++;
					
					}
					}
				if(cmd.charAt(i)==')'&&attrStart)
					break;
				
				if(cmd.charAt(i)=='('&&flag)
					{
					attrStart=true;
					index=0;
					}
				if(attrStart)
				{
					String regex="\\s*\\w+\\s*,|\\s*\\w+\\s*\\)";
						Pattern p =Pattern.compile(regex);
						Matcher m = p.matcher(cmd); 
						while(m.find())
						{
							String token=m.group();
							if(!colStart)
							{
								inputRelation= token.substring(0,token.length()-1).toLowerCase().trim();
								colStart=true;
								i=i+token.length();
							}
							else
							{
								cols[cond_index]=token.substring(0,token.length()-1);
								cond_index++;
								i=i+token.length();
							}
						}
						
					}
				
				
				}
		return cols;
	}
	
	
	public static column scanfile(String file)
	{
		column R = new column();
		String regex_name = "[A-Za-z]{1}\\w*|\"[\\w, ]*\"";
		String regex_number ="\\t?|[+|-]?[0-9]+|[+|-]?[0-9]+.[0-9]+|[+|-]?[0-9]+.?[0-9]*[e|E][-|+]?[0-9]+";
		
		try{
		    if (file == null)
		    	throw new IOException();
		    R.relationName=file;
			BufferedReader in= new BufferedReader(new FileReader(file.trim()+".csv"));
			String line =new String();
			if (((line =in.readLine())==null)){
				System.out.print("Wrong Column Name. Please check the input file\n");
				System.exit(1);
			}
			line= line.trim();
            int prt = 0, flag=0;
            String[] s0 = line.split(",");
            Pattern pattern_name = Pattern.compile(regex_name);
            Pattern pattern_num  = Pattern.compile(regex_number);
            
            for (int i=0;i<s0.length;i++){
		    	s0[i] = s0[i].trim();		    	
		    	Matcher matcher = pattern_name.matcher(s0[i]);		    	
		    	if(!matcher.matches()){
		    		System.out.print(line+"---Wrong Column, exit\n");
			    	System.exit(1);;
		    	}	    		
		    	//if (s0[i].equals("")) {		    		
		    	//	System.out.print(line+"Wrong Column, exit\n");
			    //	System.exit(1);;
		    	//}
		    }
            for (int i=0;i<s0.length;i++){
		    	if (i == 0) {
		    		R.setname(s0[i]);
		    	}else{
		    		if (R.indexofcolumn(s0[i]) >=0){
		    			System.out.print(line+"---Wrong Column, exit\n");
				    	System.exit(1);;
		    		}
		    		R.addsibling(new column(s0[i]));
		    	}		    	
		    }
            
            int line_num = 1;
            
			while ((line=in.readLine())!=null){
				line =line.trim();
				prt = 0;
				String[] s1;
				int num = R.sizeofcolumn();
				if (line.endsWith(",")) {
					line = line.concat("0");
					s1 = line.split(",");
					s1[s1.length-1]="";
				}else{
					s1= line.split(",");
				}	
				
			    int n = s1.length;
			    /* begin check whether , is in ""*/
			    for (int i =0;i< n;i++){
			    	if (s1[i].startsWith("\"")){
			    		for (int j=i+1; j<n;j++){
			    			s1[i] = s1[i].concat(","+s1[j]);
			    			if(s1[j].endsWith("\"")){
			    				//s1[i] = s1[i].concat(","+s1[j]);
			    				for (int k=i+1; k<n-(j-i);k++){
			    					s1[k] = s1[k+j-i];	
			    				}
			    				n = n-(j-i);
			    				break;
			    			}
			    		}//end for
			    	}
			    }
			    // end check and proceed the , in ""
			    if (n!= num){
			    	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
			    	line_num++;
			    	continue;
			    }
			    flag = 0;
	            for (int i=0;i<n;i++){
			    	s1[i] = s1[i].trim();		    	
			    	Matcher matcher_name = pattern_name.matcher(s1[i]);
			    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
			    	if(matcher_name.matches()){
			    		R.getcolumn(i).type_total++;
			    	}else if (matcher_num.matches()){
			    		R.getcolumn(i).type_total--;
			    	}else{
			    		flag = 1;
			    	}			    		
	            }//end for
	            if (flag ==1){	            	
	            	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
	            	line_num++;
	            	continue;
	            }
	            if (s1[0].equals("")){
	            	System.out.print("Line"+line_num+":\""+line+"\""+"--PK can't be null, skipping\n");
	            	line_num++;
	            	continue;
	            }
	           // if (R.indexofchild(s1[0], 1)>=0){
	           // 	System.out.print("Line"+line_num+":\""+line+"\""+"--Duplicate PK, skipping\n");
	           // 	line_num++;
	           // 	continue;
	           // }else{
	            	R.getcolumn(0).addchild(s1[0]);
	           // }
			    for (int ss=1;ss<n;ss++){
			    	//R.getcolumn(ss).addchild(Double.valueOf(s1[ss]));
			    	R.getcolumn(ss).addchild(s1[ss]);			    	
			    }
			    line_num++;
			}//end while	
			for( int i=0; i<R.sizeofcolumn();i++){
				if (R.getcolumn(i).type_total>=0){
					R.getcolumn(i).settype("string");
				}else{
					R.getcolumn(i).settype("float");
				}
			}
			
						    
		}catch(FileNotFoundException fnfe)
		  	{
			  System.err.println(fnfe);
			  }
		catch(IOException ioe)
			  {
			  System.err.println(ioe);
			  }
		//R.sizeofchild();
		//R.sizeofcolumn();
		return R;
}


public static BufferedWriter openforwrite(String s){
    BufferedWriter out = null;
    try{
	    if (s == null)
	    	throw new IOException();	   
	    out= new BufferedWriter(new FileWriter(s.trim().concat(".csv")));
	   
	}// end try
    catch(IOException ioe)
	  {
	  System.err.println(ioe);
	  }
    return out;	
}
private static void select(column input, String output, String cond){
	boolean flag=false, isFloat=false,iscolumn=false;
	int splitindex =extractCondOperators(cond);
	String opnd1=cond.substring(0, splitindex);
	String opnd2=cond.substring(splitindex+1, cond.length()).trim();
	char op = cond.charAt(splitindex);
	//String p =opnd1;
	int n1 = input.indexofcolumn(opnd1);
	int n2 = input.indexofcolumn(opnd2);
	try{
	BufferedWriter out = new BufferedWriter(new FileWriter(output+".csv"));
    if(n1<0)
		{
		System.exit(1);
		}
    if(n2>0)
   {
	   if(!(input.getcolumn(n1).gettype().equals(input.getcolumn(n1).gettype())))
	   {
		   System.out.print("The types of the two columns don't match.\n");
		   System.exit(1);
	   }
	   else 
		   iscolumn=true;
   }
    if(n2<0)
        {
    	if(!(input.getcolumn(n1).gettype().equals(getCondType(opnd2))))
    {
    	System.out.print("The type of the condition arguments don't match.\n");
    	System.exit(1);
    }
    if(getCondType(opnd2).equals("float"))
    	isFloat=true;
        }	
    for(int i=0;i<input.sizeofcolumn();i++)
		{
		if(i<input.sizeofcolumn()-1)
		{
			out.write(input.getcolumn(i).getname()+",");	
		}
		else
		{
			out.write(input.getcolumn(i).getname());
			out.newLine();
		}
		}
	for(int i=0;i<input.sizeofchild();i++)
	{
		switch(op)
		{
		case '=' : 
			if(iscolumn)
			{
				if(input.getcolumn(n1).gettype().equals("string"))
					{
					if(input.getcolumn(n1).getchild(i).equals(input.getcolumn(n2).getchild(i)))
					{
						flag=true;
					   }
					}
				else
					{
					if(input.getcolumn(n1).getchildfloat(i)==input.getcolumn(n2).getchildfloat(i))
						{
						flag=true;
		                }
					}
			}
		else
		{
			if(isFloat)
			{
				float f = Float.valueOf(opnd2.trim()).floatValue();
                if(!input.getcolumn(n1).getchild(i).equals("")&&(input.getcolumn(n1).getchildfloat(i)==f))
			    {
                	flag=true;
                	}
				}
		   else
		   {
			   if(!input.getcolumn(n1).getchild(i).equals("")&&(input.getcolumn(n1).getchild(i).equals(opnd2)))
				{
		    	   flag=true;
				}
			}
		}
		if(flag)
		{
			for(int j=0; j<input.sizeofcolumn();j++)
			{
			String a=input.getcolumn(j).getchild(i);
			if(j==input.sizeofcolumn()-1)
				{
				out.write(a+"\n");
				out.newLine();
				flag=false;
				}
			else
				{
			out.write(a+",");
			flag=false;
			    }
			}
		}
		break;
		case '<' : 
			if(iscolumn)
			{
				if(input.getcolumn(n1).gettype().equals("string"))
				{
				System.out.print("operation < is not applicable on strings data type.\n");
				System.exit(1);
				}
			else
				if(input.getcolumn(n1).getchildfloat(i)<input.getcolumn(n2).getchildfloat(i))
					flag=true;
			}
		else
		{
			if(isFloat)
			{
				float f = Float.valueOf(opnd2.trim()).floatValue();
				if(!input.getcolumn(n1).getchild(i).equals("")&&input.getcolumn(n1).getchildfloat(i)<f)
					flag=true;
	        }
			else
			{
			System.out.print("The operation < is not applicable on strings.\n");
			System.exit(1);
		    }
	  }
		if(flag)
		{
			for(int j=0; j<input.sizeofcolumn();j++)
			{
			String a=input.getcolumn(j).getchild(i);
			if(j==input.sizeofcolumn()-1)
			{
				out.write(a);
				out.newLine();
				flag=false;
				}
			else
			{
			out.write(a+",");
			flag=false;
			}
			}}
		break;
		case '>' :  if(iscolumn)
		{
			if(input.getcolumn(n1).gettype().equals("string"))
			{
			System.out.print("operation < is not applicable on strings data type.\n");
			System.exit(1);
			}
		else
			if(input.getcolumn(n1).getchildfloat(i)>input.getcolumn(n2).getchildfloat(i))
				flag=true;
		}
	else
	{
		if(isFloat)
		{
			float f = Float.valueOf(opnd2.trim()).floatValue();
			if(!input.getcolumn(n1).getchild(i).equals("")&&input.getcolumn(n1).getchildfloat(i)>f)
				flag=true;
        }
		else
		{
		System.out.print("The operation > is not applicable on strings.\n");
		System.exit(1);
	    }
  }
	if(flag)
	{
		for(int j=0; j<input.sizeofcolumn();j++)
		{
		String a=input.getcolumn(j).getchild(i);
		if(j==input.sizeofcolumn()-1)
		{
			out.write(a);
			out.newLine();
			flag=false;
			}
		else
		{
		out.write(a+",");
		flag=false;
		}
		}}
	break;
		default: System.out.print("Operator unknown");
		break;
		}
	}
	out.close();
	}
	catch (Exception e)
	 {
		 //Catch exception if any
	     System.err.println("Error: " + e.getMessage());
	 }
	
}
private static void join(column inputRelation1, column inputRelation2, String outputRelation, String joinCol)
{
	int n1 = inputRelation1.indexofcolumn(joinCol);
	int n2 = inputRelation2.indexofcolumn(joinCol);
	String a="";
	if(n1<0||n2<0)
	{
		System.out.print("The column doesn't exist in both the relations.\n");
		System.exit(1);
	}
	if(!inputRelation1.getcolumn(n1).gettype().equals(inputRelation2.getcolumn(n2).gettype()))
	{
		System.out.print("type didn't matched\n");
		System.exit(1);
	}
	try{
		BufferedWriter out = new BufferedWriter(new FileWriter(outputRelation+".csv"));
		for(int i=0;i<inputRelation1.sizeofcolumn();i++)
		{
			if(!inputRelation1.getcolumn(i).getname().equals(joinCol))
			out.write(inputRelation1.getcolumn(i).getname()+",");	
		
		}
		for(int i=0;i<inputRelation2.sizeofcolumn();i++)
		{
		
			if(i<inputRelation2.sizeofcolumn()-1)
		{
			out.write(inputRelation2.getcolumn(i).getname()+",");	
		}
		else
		{
			out.write(inputRelation2.getcolumn(i).getname());
			out.newLine();
		}
		}
		for(int i=0;i<inputRelation1.sizeofchild();i++)
		{
			for(int j=0;j<inputRelation2.sizeofchild();j++)
			{
				boolean flag=false;
				if(inputRelation1.getcolumn(n1).gettype().equals("string"))
				{
					if(inputRelation1.getcolumn(n1).getchild(i).equals(inputRelation2.getcolumn(n2).getchild(j)))
						flag=true;
				}
				else
				{
					if(inputRelation1.getcolumn(n1).getchildfloat(i)==inputRelation2.getcolumn(n2).getchildfloat(j))
						flag=true;
				}
				if(flag)
						{
					for(int x=0; x<inputRelation1.sizeofcolumn();x++)
					{
						if(!inputRelation1.getcolumn(x).getname().equals(joinCol))
						{
							a=inputRelation1.getcolumn(x).getchild(i);
							out.write(a+",");
						}
						
					}
					for(int y=0; y<inputRelation2.sizeofcolumn();y++)
					{
						a=inputRelation2.getcolumn(y).getchild(j);
						if(y==inputRelation2.sizeofcolumn()-1)
						{
							out.write(a);
							out.newLine();
							
							}
						else
						{
						out.write(a+",");
			    		
						}
					}
						}// end if(flag)
				flag = false;
			}
		}
		out.close();
}catch (Exception e)
{
	 //Catch exception if any
    System.err.println("Error: " + e.getMessage());
}
}

private static void EquiJoin(column inputRelation1, column inputRelation2, String outputRelation, String joinCond)
{
	int n1=0,n2=0;
	boolean flag=false;
	int splitindex =extractCondOperators(joinCond);
	char op = joinCond.charAt(splitindex);
    if(!(op=='='))
    {
    	System.out.print("An EquiJoin command cannot have any other operator other than = .\n");
    	System.exit(1);
    }
    String opnd1=joinCond.substring(0, splitindex).trim();
	String opnd2=joinCond.substring(splitindex+1, joinCond.length()).trim();
	if(checkRelationSpecified(joinCond))
	{
		int pos1=0,pos2=0;
	    String relation1="",relation2="";
		pos1=extractEquiJoinOperandPosition(opnd1);
		pos2=extractEquiJoinOperandPosition(opnd2);
		relation1=opnd1.substring(0, pos1);
		relation2=opnd2.substring(0, pos2);
		if(relation1.equals(inputRelation1.relationName)&&relation2.equals(inputRelation2.relationName))
		{
			opnd1=opnd1.substring(pos1+1, opnd1.length()).trim();
			opnd2=opnd2.substring(pos2+1, opnd2.length()).trim();
			n1 = inputRelation1.indexofcolumn(opnd1);
			n2 = inputRelation2.indexofcolumn(opnd2);
		}
		if(relation1.equals(inputRelation2.relationName)&&relation2.equals(inputRelation1.relationName))
		{
			opnd1=opnd1.substring(pos1+1, opnd1.length()).trim();
			opnd2=opnd2.substring(pos2+1, opnd2.length()).trim();
			n1 = inputRelation1.indexofcolumn(opnd2);
			n2 = inputRelation2.indexofcolumn(opnd1);
		}
		
	}
	else
	{
	n1 = inputRelation1.indexofcolumn(opnd1);
	n2 = inputRelation2.indexofcolumn(opnd2);
	}
	String a="";
	if(n1<0||n2<0)
	{
		System.out.print("The columns doesn't exist in both the relations.\n");
		System.exit(1);
	}
	if(!inputRelation1.getcolumn(n1).gettype().equals(inputRelation2.getcolumn(n2).gettype()))
	{
		System.out.print("type didn't matched\n");
		System.exit(1);
	}
	try{
		BufferedWriter out = new BufferedWriter(new FileWriter(outputRelation+".csv"));
		for(int i=0;i<inputRelation1.sizeofcolumn();i++)
		{
			out.write(inputRelation1.getcolumn(i).getname()+",");	
		
		}
		for(int i=0;i<inputRelation2.sizeofcolumn();i++)
		{
		
			if(i<inputRelation2.sizeofcolumn()-1)
		{
			out.write(inputRelation2.getcolumn(i).getname()+",");	
		}
		else
		{
			out.write(inputRelation2.getcolumn(i).getname());
			out.newLine();
		}
		}
		for(int i=0;i<inputRelation1.sizeofchild();i++)
		{
			for(int j=0;j<inputRelation2.sizeofchild();j++)
			{
				if(inputRelation1.getcolumn(n1).gettype().equals("string"))
				{
					if(inputRelation1.getcolumn(n1).getchild(i).equals(inputRelation2.getcolumn(n2).getchild(j)))
						flag=true;
				}
				else
				{
					if(inputRelation1.getcolumn(n1).getchildfloat(i)==inputRelation2.getcolumn(n2).getchildfloat(j))
						flag=true;
				}
				if(flag)
						{
					for(int x=0; x<inputRelation1.sizeofcolumn();x++)
					{
						
							a=inputRelation1.getcolumn(x).getchild(i);
							out.write(a+",");
												
					}
					for(int y=0; y<inputRelation2.sizeofcolumn();y++)
					{
						a=inputRelation2.getcolumn(y).getchild(j);
						if(y==inputRelation2.sizeofcolumn()-1)
						{
							out.write(a);
							out.newLine();
							flag=false;
							}
						else
						{
						out.write(a+",");
			    		flag=false;
						}
					}
						}
			}
		}
		out.close();
}catch (Exception e)
{
	 //Catch exception if any
    System.err.println("Error: " + e.getMessage());
}
}
private static int extractCondOperators(String cond)
{
	int index=0;
	if(cond.contains("="))
	index=cond.indexOf('=');
	if(cond.contains("<"))
		index=cond.indexOf('<');
	if(cond.contains(">"))
		index=cond.indexOf('>');
	return index;
	
}
private static String getCondType(String opnd2)
{
	String curr_type="";
	String regex1="\\s*\\w+\\s*";
	String regex2="[1-9]+[0-9]*";
	Pattern p =Pattern.compile(regex1);
	Matcher m = p.matcher(opnd2); 
	if(m.find())
	curr_type="string";
	p =Pattern.compile(regex2);
	m = p.matcher(opnd2); 
	if(m.find())
	curr_type="float";
	return curr_type;
}
private static boolean checkRelationSpecified(String joinCond)
{
	String regex1="\\s*\\w+\\.\\w+\\s*=\\s*\\w+\\.\\w+\\s*";
	Pattern p =Pattern.compile(regex1);
	Matcher m = p.matcher(joinCond); 
	if(m.find())
		return true;
	else 
		return false;
	
}
private static int extractEquiJoinOperandPosition(String opnd)
{
	int index=0;
	if(opnd.contains("."))
	index=opnd.indexOf('.');
	return index;
}
private static void extractSum(String cmd)
{
	String aggrCol ="";
	String groupCol="";
	int pos1=0,pos2=0;
	pos1 = cmd.indexOf('=');
	System.out.print("index = "+pos1+"\n");
	outputRelation = cmd.substring(0, pos1);
	outputRelation =outputRelation.toLowerCase(); 
	System.out.print("outputRelation = "+outputRelation+"\n");
	pos1 = cmd.indexOf('(');
	pos2 = cmd.indexOf(',');
	System.out.print("index1 = "+pos1+"\tindex2 = "+pos2+"\n");
	inputRelation = cmd.substring(pos1+1, pos2).trim();
	inputRelation =inputRelation.toLowerCase();
	System.out.print("inputRelation = "+inputRelation+"\n");
	pos1 = cmd.indexOf(',', pos2+1);
	groupCol = cmd.substring(pos2+1, pos1).trim();
	pos2 = cmd.indexOf(')');
	aggrCol =cmd.substring(pos1+1, pos2).trim();
	//System.out.print("aggrCol = "+aggrCol+"\n");
	if(groupCol.equals("")){
		// sum(R,,X1)
		column input = scanfile(inputRelation.trim());
		sum(input,outputRelation.trim(),aggrCol.trim());
	}else if(aggrCol.equals("1")){
		// sum(R,A,1)
		transformtoBin(inputRelation.trim());
		indexCol(inputRelation.trim(),groupCol);
		groupBySum(outputRelation.trim(),inputRelation.trim(),groupCol);
		
	}else{
		// sum(R,A,X1)
		transformtoBin(inputRelation.trim());
		indexCol(inputRelation.trim(),groupCol);
		groupBySumAggr(outputRelation.trim(),inputRelation.trim(),groupCol,aggrCol);
	}
}
private static void extractSort(String cmd)
{
	
	String sortCol="";
	int pos1=0,pos2=0;
	pos1 = cmd.indexOf('(');
	pos2 = cmd.indexOf(',');
	System.out.print("index1 = "+pos1+"\tindex2 = "+pos2+"\n");
	inputRelation = cmd.substring(pos1+1, pos2).trim();
	inputRelation =inputRelation.toLowerCase();
	System.out.print("inputRelation = "+inputRelation+"\n");
	pos1 = cmd.indexOf(')', pos2+1);
	sortCol = cmd.substring(pos2+1, pos1).trim();
	transformtoBin2(inputRelation);
	sortFile(inputRelation,sortCol);
	System.out.print("finish sort");
	
}

private static void extractIndex(String cmd)
{
	
	String sortCol="";
	int pos1=0,pos2=0;
	pos1 = cmd.indexOf('(');
	pos2 = cmd.indexOf(',');
	System.out.print("index1 = "+pos1+"\tindex2 = "+pos2+"\n");
	inputRelation = cmd.substring(pos1+1, pos2).trim();
	inputRelation =inputRelation.toLowerCase();
	System.out.print("inputRelation = "+inputRelation+"\n");
	pos1 = cmd.indexOf(')', pos2+1);
	sortCol = cmd.substring(pos2+1, pos1).trim();
	transformtoBin2(inputRelation);
	indexFile(inputRelation,sortCol);
	System.out.print("finish index");
	
}


private static void sum(column input, String output, String aggrCol){
	int n1 = input.indexofcolumn(aggrCol);
	float sum =0;
	try{
		BufferedWriter out = new BufferedWriter(new FileWriter(output+".csv"));
	    if(n1<0)
			{
			System.out.print("The column with this name "+aggrCol+"does not exist.\n");
			System.exit(1);
			}
	    else
	    	if((input.getcolumn(n1).gettype().equals("string")))
	 	   {
	 		   System.out.print("The data type of the column is string. Cannot aggregate.\n");
	 		   System.exit(1);
	 	   }
	 	   else 
	 		   {
	 		  
	 		   for(int i=0;i<input.sizeofcolumn();i++)
	 			   {
	 			   if(i==n1)
	 			   	{
	 				  System.out.print("Found the column.\n");
	 				 System.out.print(input.getcolumn(i).getname()+"\n");
	 				 out.write(input.getcolumn(i).getname());
	 				 out.newLine();
	 			   	}
	 			   }
	 		  for(int i=0;i<input.sizeofchild();i++)
 			  {
 				  sum += input.getcolumn(n1).getchildfloat(i);
 			  }
	 		 System.out.print("Found the aggregate.\n");
				 System.out.print(sum+"\n");
				 out.write(""+sum);
			out.newLine();
				}
	    out.close();
	}
	catch (Exception e)
	 {
		 //Catch exception if any
	     System.err.println("Error: " + e.getMessage());
	 }
	}



public static column scanfile2(String file, String groupCol)
{
	column R = new column();
	String regex_name = "[A-Za-z]{1}\\w*|\"[\\w, ]*\"";
	String regex_number ="\\t?|[+|-]?[0-9]+|[+|-]?[0-9]+.[0-9]+|[+|-]?[0-9]+.?[0-9]*[e|E][-|+]?[0-9]+";
	int index_groupCol=0;
	int flag1=0;
	
	try{
	    if (file == null)
	    	throw new IOException();
	    R.relationName=file;
		BufferedReader in= new BufferedReader(new FileReader(file.trim()+".csv"));
		String line =new String();
		if (((line =in.readLine())==null)){
			System.out.print("Wrong Column Name. Please check the input file\n");
			System.exit(1);
		}
		line= line.trim();
        int prt = 0, flag=0;
        String[] s0 = line.split(",");
        Pattern pattern_name = Pattern.compile(regex_name);
        Pattern pattern_num  = Pattern.compile(regex_number);
        
        for (int i=0;i<s0.length;i++){
	    	s0[i] = s0[i].trim();		    	
	    	Matcher matcher = pattern_name.matcher(s0[i]);		    	
	    	if(!matcher.matches()){
	    		System.out.print(line+"---Wrong Column, exit\n");
		    	System.exit(1);;
	    	}
	    	if (s0[i].equals(groupCol)){
	    	    index_groupCol = i;
	    	    flag1++;
	    	}
	    	//if (s0[i].equals("")) {		    		
	    	//	System.out.print(line+"Wrong Column, exit\n");
		    //	System.exit(1);;
	    	//}
	    }
        if (flag1 != 1){
        	System.out.print(line+"---Wrong Column, exit\n");
            System.exit(1);
        }
        R.setname(s0[index_groupCol]);
        R.addsibling(new column("SumofRow"));
        int num = s0.length;
        int line_num = 1;
        
		while ((line=in.readLine())!=null){
			line =line.trim();
			prt = 0;
			String[] s1;
			//int num = R.sizeofcolumn();
			if (line.endsWith(",")) {
				line = line.concat("0");
				s1 = line.split(",");
				s1[s1.length-1]="";
			}else{
				s1= line.split(",");
			}	
			
		    int n = s1.length;
		    /* begin check whether , is in ""*/
		    for (int i =0;i< n;i++){
		    	if (s1[i].startsWith("\"")){
		    		for (int j=i+1; j<n;j++){
		    			s1[i] = s1[i].concat(","+s1[j]);
		    			if(s1[j].endsWith("\"")){
		    				//s1[i] = s1[i].concat(","+s1[j]);
		    				for (int k=i+1; k<n-(j-i);k++){
		    					s1[k] = s1[k+j-i];	
		    				}
		    				n = n-(j-i);
		    				break;
		    			}
		    		}//end for
		    	}
		    }
		    // end check and proceed the , in ""
		    if (n!= num){
		    	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
		    	line_num++;
		    	continue;
		    }
		    flag = 0;
            for (int i=index_groupCol;i<index_groupCol+1;i++){
		    	s1[i] = s1[i].trim();		    	
		    	Matcher matcher_name = pattern_name.matcher(s1[i]);
		    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
		    	if(matcher_name.matches()){
		    		R.getcolumn(0).type_total++;
		    	}else if (matcher_num.matches()){
		    		R.getcolumn(0).type_total--;
		    	}else{
		    		flag = 1;
		    	}			    		
            }//end for
            if (flag ==1){	            	
            	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
            	line_num++;
            	continue;
            }
           // if (s1[0].equals("")){
           // 	System.out.print("Line"+line_num+":\""+line+"\""+"--PK can't be null, skipping\n");
           // 	line_num++;
           // 	continue;
           // }
           // if (R.indexofchild(s1[0], 1)>=0){
           // 	System.out.print("Line"+line_num+":\""+line+"\""+"--Duplicate PK, skipping\n");
           // 	line_num++;
           // 	continue;
           // }else{
           // 	R.getcolumn(0).addchild(s1[0]);
           // }
            float pos = indexsort(R, s1[index_groupCol]);
            if (pos == (int)pos){
            	R.getcolumn(1).incchild((int)pos);
            }else{
            	R.getcolumn(0).insert((int)(pos+0.5),s1[index_groupCol]);
            	R.getcolumn(1).insert((int)(pos+0.5),"1");
            }
		    
		    line_num++;
		}//end while	
		
//		for( int i=0; i<R.sizeofcolumn();i++){
//			if (R.getcolumn(i).type_total>=0){
//				R.getcolumn(i).settype("string");
//			}else{
//				R.getcolumn(i).settype("float");
//			}
//		}
		
					    
	}catch(FileNotFoundException fnfe)
	  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
	//R.sizeofchild();
	//R.sizeofcolumn();
	return R;
}
public static column scanfile3(String file, String groupCol, String aggrCol)
{
	column R = new column();
	String regex_name = "[A-Za-z]{1}\\w*|\"[\\w, ]*\"";
	String regex_number ="\\t?|[+|-]?[0-9]+|[+|-]?[0-9]+.[0-9]+|[+|-]?[0-9]+.?[0-9]*[e|E][-|+]?[0-9]+";
	int index_groupCol=0;
	int index_aggrCol=0;
	int flag1=0;
	int flag2=0;
	
	try{
	    if (file == null)
	    	throw new IOException();
	    R.relationName=file;
		BufferedReader in= new BufferedReader(new FileReader(file.trim()+".csv"));
		String line =new String();
		if (((line =in.readLine())==null)){
			System.out.print("Wrong Column Name. Please check the input file\n");
			System.exit(1);
		}
		line= line.trim();
        int prt = 0, flag=0;
        String[] s0 = line.split(",");
        Pattern pattern_name = Pattern.compile(regex_name);
        Pattern pattern_num  = Pattern.compile(regex_number);
        
        for (int i=0;i<s0.length;i++){
	    	s0[i] = s0[i].trim();		    	
	    	Matcher matcher = pattern_name.matcher(s0[i]);		    	
	    	if(!matcher.matches()){
	    		System.out.print(line+"---Wrong Column, exit\n");
		    	System.exit(1);;
	    	}
	    	if (s0[i].equals(groupCol)){
	    	    index_groupCol = i;
	    	    flag1++;
	    	}
	    	if (s0[i].equals(aggrCol)){
	    		index_aggrCol = i;
	    	    flag2++;
	    	}
	    	//if (s0[i].equals("")) {		    		
	    	//	System.out.print(line+"Wrong Column, exit\n");
		    //	System.exit(1);;
	    	//}
	    }
        if (flag1 != 1||flag2 !=1 ){ //the column doesn't exis!
        	System.out.print(line+"---Wrong Column, exit\n");
            System.exit(1);
        }
        R.setname(s0[index_groupCol]);
        R.addsibling(new column("sum_"+s0[index_aggrCol]));
        int num = s0.length;
        int line_num = 1;
        
		while ((line=in.readLine())!=null){
			line =line.trim();
			prt = 0;
			String[] s1;
			//int num = R.sizeofcolumn();
			if (line.endsWith(",")) {
				line = line.concat("0");
				s1 = line.split(",");
				s1[s1.length-1]="";
			}else{
				s1= line.split(",");
			}	
			
		    int n = s1.length;
		    /* begin check whether , is in ""*/
		    for (int i =0;i< n;i++){
		    	if (s1[i].startsWith("\"")){
		    		for (int j=i+1; j<n;j++){
		    			s1[i] = s1[i].concat(","+s1[j]);
		    			if(s1[j].endsWith("\"")){
		    				//s1[i] = s1[i].concat(","+s1[j]);
		    				for (int k=i+1; k<n-(j-i);k++){
		    					s1[k] = s1[k+j-i];	
		    				}
		    				n = n-(j-i);
		    				break;
		    			}
		    		}//end for
		    	}
		    }
		    // end check and proceed the , in ""
		    if (n!= num){
		    	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
		    	line_num++;
		    	continue;
		    }
		    flag = 0;
            for (int i=index_groupCol;i<index_groupCol+1;i++){
		    	s1[i] = s1[i].trim();		    	
		    	Matcher matcher_name = pattern_name.matcher(s1[i]);
		    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
		    	if(matcher_name.matches()){
		    		R.getcolumn(0).type_total++;
		    	}else if (matcher_num.matches()){
		    		R.getcolumn(0).type_total--;
		    	}else{
		    		flag = 1;
		    	}			    		
            }//end for
            flag1 = 0;
            for (int i=index_aggrCol;i<index_aggrCol+1;i++){
		    	s1[i] = s1[i].trim();		    	
		    	Matcher matcher_name = pattern_name.matcher(s1[i]);
		    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
		    	if(matcher_name.matches()){
		    		R.getcolumn(1).type_total++;
		    	}else if (matcher_num.matches()){
		    		R.getcolumn(1).type_total--;
		    	}else{
		    		flag1 = 1;
		    	}			    		
            }//end for
            if (flag ==1||flag1==1){	            	
            	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
            	line_num++;
            	continue;
            }
           // if (s1[0].equals("")){
           // 	System.out.print("Line"+line_num+":\""+line+"\""+"--PK can't be null, skipping\n");
           // 	line_num++;
           // 	continue;
           // }
           // if (R.indexofchild(s1[0], 1)>=0){
           // 	System.out.print("Line"+line_num+":\""+line+"\""+"--Duplicate PK, skipping\n");
           // 	line_num++;
           // 	continue;
           // }else{
           // 	R.getcolumn(0).addchild(s1[0]);
           // }
            if(R.getcolumn(1).type_total>0){
            	System.out.print("Sum column cannot be string type");
            	System.exit(1);
            }
            float pos = indexsort(R, s1[index_groupCol]);
            if (pos == (int)pos){
            	R.getcolumn(1).sumchild((int)pos, Float.parseFloat(s1[index_aggrCol]));
            }else{
            	R.getcolumn(0).insert((int)(pos+0.5),s1[index_groupCol]);
            	R.getcolumn(1).insert((int)(pos+0.5),s1[index_aggrCol]);
            }
		    
		    line_num++;
		}//end while	
		
//		for( int i=0; i<R.sizeofcolumn();i++){
//			if (R.getcolumn(i).type_total>=0){
//				R.getcolumn(i).settype("string");
//			}else{
//				R.getcolumn(i).settype("float");
//			}
//		}
		
					    
	}catch(FileNotFoundException fnfe)
	  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
	//R.sizeofchild();
	//R.sizeofcolumn();
	return R;
}

public static float indexsort(column R, String groupCol){
	float index=0.0f;
	if(R.sizeofchild()==0)
		return -0.5f;
	if(R.type_total<0){
		// number
		index = binsearch(R,0,R.sizeofchild()-1,Float.parseFloat(groupCol));
	}else if (R.type_total>0){
		// string
		index = binsearch2(R,0,R.sizeofchild()-1,groupCol);
	}
		
	return index;
}
public static float binsearch (column R, int i, int j, float f){
	float index =0.0f;
	int k =(i+j)/2;
	if (Float.parseFloat(R.getcolumn(0).getchild(k)) ==f){
		return k;
	}
	if ( i >= j){
		if (Float.parseFloat(R.getcolumn(0).getchild(i)) >f){
			return i-0.5f;
		}	else{
			return i+0.5f;
		}
	}
		
    if (Float.parseFloat(R.getcolumn(0).getchild(k)) >f){
		index = binsearch(R,i,k-1,f);
	}else{
		index = binsearch(R,k+1,j,f);
	}
	return index;
	
}
public static float binsearch2 (column R, int i, int j, String f){
	float index =0.0f;
	int k =(i+j)/2;
	if (R.getcolumn(0).getchild(k).equals(f)){
		return k;
	}
	if ( i >= j){
		if (R.getcolumn(0).getchild(i).compareTo(f)>0){
			return i-0.5f;
		}	else{
			return i+0.5f;
		}
	}
		
    if (R.getcolumn(0).getchild(k).compareTo(f)>0){
		index = binsearch2(R,i,k-1,f);
	}else{
		index = binsearch2(R,k+1,j,f);
	}
	return index;
	
}
public static void transformtoBin(String file){
	String regex_name = "[A-Za-z]{1}\\w*|\"[\\w, ]*\"";
	String regex_number ="\\t?|[+|-]?[0-9]+|[+|-]?[0-9]+.[0-9]+|[+|-]?[0-9]+.?[0-9]*[e|E][-|+]?[0-9]+";
	
	try{
	    if (file == null)
	    	throw new IOException();
	    BufferedReader in= new BufferedReader(new FileReader(file.trim()+".csv"));
	    BufferedWriter out1 = new BufferedWriter(new FileWriter("IR_Head_"+file.trim()+".bin"));
	    DataOutputStream out2 = new DataOutputStream(new FileOutputStream("IR_Data_"+file.trim()+".bin"));
		String line =new String();
		if (((line =in.readLine())==null)){
			System.out.print("Wrong Column Name. Please check the input file\n");
			System.exit(1);
		}
		line= line.trim();
        int prt = 0, flag=0;
        String[] s0 = line.split(",");
        int[] s0_type_total = new int[s0.length];
        String[] s0_type = new String[s0.length];
        Pattern pattern_name = Pattern.compile(regex_name);
        Pattern pattern_num  = Pattern.compile(regex_number);
        
        for (int i=0;i<s0.length;i++){
	    	s0[i] = s0[i].trim();		    	
	    	Matcher matcher = pattern_name.matcher(s0[i]);		    	
	    	if(!matcher.matches()){
	    		System.out.print(line+"---Wrong Column, exit\n");
		    	System.exit(1);;
	    	}	    		
	    }
        
        for (int i=0;i<s0.length;i++){
        	if (i == 0) {
        		out1.write(s0[i]);
        	}else{
        	    out1.write(","+s0[i]);	  
        	}		    	
        }
        out1.write("\n");
        out1.flush();
               
        int line_num = 1;
        int record_num =0;
		while ((line=in.readLine())!=null){
			line =line.trim();
			prt = 0;
			String[] s1;
			int num = s0.length;
			if (line.endsWith(",")) {
				line = line.concat("0");
				s1 = line.split(",");
				s1[s1.length-1]="";
			}else{
				s1= line.split(",");
			}	
			
		    int n = s1.length;
		    /* begin check whether , is in ""*/
		    for (int i =0;i< n;i++){
		    	if (s1[i].startsWith("\"")){
		    		for (int j=i+1; j<n;j++){
		    			s1[i] = s1[i].concat(","+s1[j]);
		    			if(s1[j].endsWith("\"")){
		    				//s1[i] = s1[i].concat(","+s1[j]);
		    				for (int k=i+1; k<n-(j-i);k++){
		    					s1[k] = s1[k+j-i];	
		    				}
		    				n = n-(j-i);
		    				break;
		    			}
		    		}//end for
		    	}
		    }
		    // end check and proceed the , in ""
		    if (n!= num){
		    	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
		    	line_num++;
		    	continue;
		    }
		    flag = 0;
            for (int i=0;i<n;i++){
		    	s1[i] = s1[i].trim();		    	
		    	Matcher matcher_name = pattern_name.matcher(s1[i]);
		    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
		    	if(matcher_name.matches()){
		    		//R.getcolumn(i).type_total++;
		    		s0_type_total[i]++;
		    	}else if (matcher_num.matches()){
		    		//R.getcolumn(i).type_total--;
		    		s0_type_total[i]--;
		    	}else{
		    		flag = 1;
		    	}			    		
            }//end for
            if (flag ==1){	            	
            	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
            	line_num++;
            	continue;
            }
            if (s1[0].equals("")){
            	System.out.print("Line"+line_num+":\""+line+"\""+"--PK can't be null, skipping\n");
            	line_num++;
            	continue;
            }
                       
		    for (int ss=0;ss<n;ss++){
		    	//R.getcolumn(ss).addchild(Double.valueOf(s1[ss]));
		    	//R.getcolumn(ss).addchild(s1[ss]);
		    	if(s0_type_total[ss]>0){
		    		//string
		    		int num2 = 20-s1[ss].length();
		    		for(int i=0; i<num2;i++)
		    			s1[ss]+="\t";
		    		out2.writeBytes(s1[ss]);
		    	}else {
		    		//number
		    		out2.writeDouble(Double.parseDouble(s1[ss]));
		    		out2.flush();
		    		
		    	}
		    }
		    record_num++;
		    line_num++;
		}//end while	
		for( int i=0; i<s0.length;i++){
			if (s0_type_total[i]>=0){
				s0_type[i] ="string";
			}else{
				s0_type[i] ="float";
			}
			if (i==0){
				out1.write(s0_type[i]);
			}else{
				out1.write(","+s0_type[i]);
			}
		}
		out1.newLine();
		out1.write(Integer.toString(record_num));
		in.close();
		out1.close();
		out2.close();
		
		
					    
	}catch(FileNotFoundException fnfe)
	  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }

}

public static void transformtoBin2(String file){
	String regex_name = "[A-Za-z]{1}\\w*|\"[\\w, ]*\"";
	String regex_number ="\\t?|[+|-]?[0-9]+|[+|-]?[0-9]+.[0-9]+|[+|-]?[0-9]+.?[0-9]*[e|E][-|+]?[0-9]+";
	char Maker = '*';
	try{
	    if (file == null)
	    	throw new IOException();
	    /* judge whether the file exist----------------------------*/
	    File file1 = new File("IR_Head2_"+file.trim()+".bin");
	    File file2 = new File("IR_Data2_"+file.trim()+".bin");
	    if(file1.exists()&&file2.exists())
	    	return;
	    /*---------------------------------------------------------*/	
	    BufferedReader in= new BufferedReader(new FileReader(file.trim()+".csv"));
	    BufferedWriter out1 = new BufferedWriter(new FileWriter("IR_Head2_"+file.trim()+".bin"));
	    DataOutputStream out2 = new DataOutputStream(new FileOutputStream("IR_Data2_"+file.trim()+".bin"));
		String line =new String();
		if (((line =in.readLine())==null)){
			System.out.print("Wrong Column Name. Please check the input file\n");
			System.exit(1);
		}
		line= line.trim();
        int prt = 0, flag=0;
        String[] s0 = line.split(",");
        int[] s0_type_total = new int[s0.length];
        String[] s0_type = new String[s0.length];
        Pattern pattern_name = Pattern.compile(regex_name);
        Pattern pattern_num  = Pattern.compile(regex_number);
        
        for (int i=0;i<s0.length;i++){
	    	s0[i] = s0[i].trim();		    	
	    	Matcher matcher = pattern_name.matcher(s0[i]);		    	
	    	if(!matcher.matches()){
	    		System.out.print(line+"---Wrong Column, exit\n");
		    	System.exit(1);;
	    	}	    		
	    }
        
        for (int i=0;i<s0.length;i++){
        	if (i == 0) {
        		out1.write(s0[i]);
        	}else{
        	    out1.write(","+s0[i]);	  
        	}		    	
        }
        out1.write("\n");
        out1.flush();
               
        int line_num = 1;
        int record_num =0;
		while ((line=in.readLine())!=null){
			line =line.trim();
			prt = 0;
			String[] s1;
			int num = s0.length;
			if (line.endsWith(",")) {
				line = line.concat("0");
				s1 = line.split(",");
				s1[s1.length-1]="";
			}else{
				s1= line.split(",");
			}	
			
		    int n = s1.length;
		    /* begin check whether , is in ""*/
		    for (int i =0;i< n;i++){
		    	if (s1[i].startsWith("\"")){
		    		for (int j=i+1; j<n;j++){
		    			s1[i] = s1[i].concat(","+s1[j]);
		    			if(s1[j].endsWith("\"")){
		    				//s1[i] = s1[i].concat(","+s1[j]);
		    				for (int k=i+1; k<n-(j-i);k++){
		    					s1[k] = s1[k+j-i];	
		    				}
		    				n = n-(j-i);
		    				break;
		    			}
		    		}//end for
		    	}
		    }
		    // end check and proceed the , in ""
		    if (n!= num){
		    	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
		    	line_num++;
		    	continue;
		    }
		    flag = 0;
            for (int i=0;i<n;i++){
		    	s1[i] = s1[i].trim();		    	
		    	Matcher matcher_name = pattern_name.matcher(s1[i]);
		    	Matcher matcher_num  = pattern_num.matcher(s1[i]);
		    	if(matcher_name.matches()){
		    		//R.getcolumn(i).type_total++;
		    		s0_type_total[i]++;
		    	}else if (matcher_num.matches()){
		    		//R.getcolumn(i).type_total--;
		    		s0_type_total[i]--;
		    	}else{
		    		flag = 1;
		    	}			    		
            }//end for
            if (flag ==1){	            	
            	System.out.print("Line"+line_num+":\""+line+"\""+"--Data of this line are wrong, skipping\n");
            	line_num++;
            	continue;
            }
            if (s1[0].equals("")){
            	System.out.print("Line"+line_num+":\""+line+"\""+"--PK can't be null, skipping\n");
            	line_num++;
            	continue;
            }
            out2.writeChar(Maker) ;          
		    for (int ss=0;ss<n;ss++){
		    	//R.getcolumn(ss).addchild(Double.valueOf(s1[ss]));
		    	//R.getcolumn(ss).addchild(s1[ss]);
		    	if(s0_type_total[ss]>0){
		    		//string
		    		int num2 = 20-s1[ss].length();
		    		for(int i=0; i<num2;i++)
		    			s1[ss]+="\t";
		    		out2.writeBytes(s1[ss]);
		    	}else {
		    		//number
		    		out2.writeDouble(Double.parseDouble(s1[ss]));
		    		out2.flush();
		    		
		    	}
		    }
		    record_num++;
		    line_num++;
		}//end while	
		for( int i=0; i<s0.length;i++){
			if (s0_type_total[i]>=0){
				s0_type[i] ="string";
			}else{
				s0_type[i] ="float";
			}
			if (i==0){
				out1.write(s0_type[i]);
			}else{
				out1.write(","+s0_type[i]);
			}
		}
		out1.newLine();
		out1.write(Integer.toString(record_num));
		in.close();
		out1.close();
		out2.close();
		
		
					    
	}catch(FileNotFoundException fnfe)
	  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }

}

public static void indexCol(String file, String groupCol){
	
	try{
		 BufferedReader in= new BufferedReader(new FileReader("IR_Head_"+file.trim()+".bin"));
		 String line =new String();
		 line =in.readLine();
		 String[] s0 = line.split(",");
		 line =in.readLine();
		 String[] s1 = line.split(",");
		 line =in.readLine();
		 int record_num = Integer.parseInt(line);
		 column R =new column();
		 int index_groupCol = -1; 	//index of groupCol in the table
		 int flag =0;
		 for (int i=0;i<s0.length;i++){
			 if (groupCol.equals(s0[i])){
				 index_groupCol = i ;
				 flag++;
			 }
		 }
		 if (flag!= 1){
			 System.out.print("wrong group column, exit!");
			 System.exit(1);
		 }
		 R.setname(s1[index_groupCol]);
		 R.settype(s1[index_groupCol]);
		 if(s1[index_groupCol].equals("string")){
			 R.settype("string");
			 R.type_total = 10;
		 }else if (s1[index_groupCol].equals("float")){
			 R.settype("string");
			 R.type_total = -10; 
		 }
		 R.addsibling(new column("num_Record"));
		 R.getcolumn(1).settype("float");
		 
		 int totallength_record = 0;
		 int length_groupCol = 0;
		 int startof_groupCol = 0 ;
		 for (int i=0; i<s1.length;i++){
			 if (s1[i].equals("float")){
				 totallength_record += 8;
				 if (i< index_groupCol)
					 startof_groupCol += 8;
			 }else{
				 totallength_record += 20;
				 if (i< index_groupCol)
					 startof_groupCol += 20;
			 }
		 }
		 if (s1[index_groupCol].equals("float"))
			 length_groupCol = 8;
		 if (s1[index_groupCol].equals("string"))
			 length_groupCol = 20;
		 DataInputStream in2 = new DataInputStream(new FileInputStream("IR_Data_"+file.trim()+".bin"));
		 
		 float pos = -1.f;
		 double x =-1.;
		 byte[] b = new byte[20];
		 in2.skipBytes(startof_groupCol);
		 for (int i=0; i<record_num;i++){
			 if (length_groupCol == 8){
				 x = in2.readDouble();
				 pos = indexsort(R,Double.toString(x));
				 if (pos == (int)pos){
					 while(x == R.getcolumn(0).getchildfloat((int)pos)){
						 if (i>R.getcolumn(1).getchildfloat((int)pos)){
							 pos += 1;
							 if(pos==R.sizeofchild()){
								 break;
							 }
						 }else {
							 if(pos-1>=0 && x == R.getcolumn(0).getchildfloat((int)(pos-1))){
								 pos -= 1; 
							 }else{
								 break;
							 }							 
						 }
					 }
		            R.getcolumn(0).insert((int)pos, Double.toString(x));
		            R.getcolumn(1).insert((int)pos, Integer.toString(i));
		            }else{
		            	R.getcolumn(0).insert((int)(pos+0.5), Double.toString(x));
		            	R.getcolumn(1).insert((int)(pos+0.5), Integer.toString(i));
		            }
			 }else if (length_groupCol == 20){
				 in2.read(b, 0, 20);
				 
				 String temp =new String();
				 for (int k=0; k<20;k++){
					 if (b[k]==9){
						 break;
					 }
					 temp+=(char)b[k];
				 }
				 pos = indexsort(R,temp); 
				 if (pos == (int)pos){
		            	R.getcolumn(0).insert((int)pos, temp);
		            	R.getcolumn(1).insert((int)pos, Integer.toString(i));
		            }else{
		            	R.getcolumn(0).insert((int)(pos+0.5), temp);
		            	R.getcolumn(1).insert((int)(pos+0.5), Integer.toString(i));
		            }
			 }
			 in2.skipBytes(totallength_record - length_groupCol);
			 
		 }
		 BufferedWriter out = new BufferedWriter(new FileWriter("IR_Index_"+groupCol+"_"+file.trim()+".ndx"));
		 out.write(groupCol+","+"index"+"\n");
		 for (int j=0; j<R.sizeofchild();j++){
			 out.write(R.getcolumn(0).getchild(j)+","+R.getcolumn(1).getchildint(j)+"\n");				 
		 }
		 in.close();
		 in2.close();
		 out.close();		 
	}catch(FileNotFoundException fnfe)
  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
}
public static void groupBySum(String relationout,String relationin,String groupCol){
	try{
		BufferedReader in = new BufferedReader(new FileReader("IR_Index_"+groupCol+"_"+relationin+".ndx"));
		BufferedWriter out = new BufferedWriter(new FileWriter(relationout+".csv"));
		String line =in.readLine();
		String[] s0 = line.split(",");
		out.write(groupCol+",sumofRow\n");
		line = in.readLine();
		s0 = line.split(",");
		line = in.readLine();
		int num=1;
		
		while(line!=null){
			String[] s1 = line.split(",");
			if(s1[0].equals(s0[0])){
				num++;
				line = in.readLine();
				continue;
			}else{
				out.write(s0[0]+","+Integer.toString(num)+"\n");
				s0[0] = s1[0];
				s0[1] = s1[1];
				num = 0;
			}
			
		}
		out.write(s0[0]+","+Integer.toString(num)+"\n");
		in.close();
		out.close();
	}catch(FileNotFoundException fnfe)
  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
}

public static void groupBySumAggr(String outputRelation,String inputRelation,String groupCol,String aggrCol){
	try{
		BufferedReader in = new BufferedReader(new FileReader("IR_Index_"+groupCol+"_"+inputRelation+".ndx"));
		BufferedReader in2 = new BufferedReader(new FileReader("IR_Head_"+inputRelation+".bin"));
		BufferedWriter out = new BufferedWriter(new FileWriter(outputRelation+".csv"));
		String[] s0 = in2.readLine().split(",");
		String[] s1 = in2.readLine().split(",");
		String  s2 = in2.readLine();
		int index_aggr=-1;
		int flag=0;
		int total_length=0;
		int start_aggr=0;
		for (int i = 0; i<s0.length;i++){
			if (aggrCol.equals(s0[i])){
				index_aggr = i;
				flag++;				
			}
			if (s1[i].equals("string")){
				total_length +=20;				
			}else{
				total_length +=8;
			}
		}
		for (int i =0; i<index_aggr;i++){
			if (s1[i].equals("string")){
				start_aggr +=20;				
			}else{
				start_aggr +=8;
			}
		}
		if(flag!=1|s1[index_aggr].equals("string")){
			System.out.print("wrong aggregate column, exit");
			System.exit(1);
		}
		RandomAccessFile ra = new RandomAccessFile("IR_Data_"+inputRelation+".bin", "rw"); 
		out.write(groupCol+",sum_"+aggrCol+"\n");
		String line = in.readLine();
		line = in.readLine();
		String[] r0 = line.split(",");
		line = in.readLine();
		double num = readpos(ra,Double.parseDouble(r0[1])*total_length,start_aggr) ;
		while (line!=null){
			String[] r1 = line.split(",");
			if(r1[0].equals(r0[0])){
				line = in.readLine();
				num += readpos(ra,Double.parseDouble(r1[1])*total_length,start_aggr);
				continue;
			}else{
				out.write(r0[0]+","+Double.toString(num)+"\n");
				r0[0] = r1[0];
				num = 0;
			}
		}
		out.write(r0[0]+","+Double.toString(num)+"\n");
		out.close();
		
	}catch(FileNotFoundException fnfe)
  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
}
public static double readpos(RandomAccessFile ra, double record, double offset){
	double x=0.;
	try{
		
		ra.seek((long)(record+offset));
		x = ra.readDouble();
		return x;
	}catch(Exception e){
		System.err.println(e);
	}
	return x;
}
public static void sortFile(String R, String sortCol){
	try{
		
		BufferedReader in2 = new BufferedReader(new FileReader("IR_Head2_"+inputRelation+".bin"));
		
		String[] s0 = in2.readLine().split(",");
		String[] s1 = in2.readLine().split(",");
		String  s2 = in2.readLine();
		
		int index_sort=-1;
		int flag=0;
		int total_length=0;
		int start_sort=0;
		for (int i = 0; i<s0.length;i++){
			if (sortCol.equals(s0[i])){
				index_sort = i;
				flag++;				
			}
			if (s1[i].equals("string")){
				total_length +=20;				
			}else{
				total_length +=8;
			}
		}
		for (int i =0; i<index_sort;i++){
			if (s1[i].equals("string")){
				start_sort +=20;				
			}else{
				start_sort +=8;
			}
		}
		//if(flag!=1|s1[index_sort].equals("string")){
		//	System.out.print("wrong sort column, exit");
		//	System.exit(1);
		//}
		total_length +=2;	//add one position for marker
		start_sort+=2;   // add one position for marker
		int bufsize = 5000;
		byte[] buf = new byte[bufsize*total_length];
		DataInputStream in3 = new DataInputStream(new FileInputStream("IR_Data2_"+inputRelation+".bin"));
		int off = 0;
		int num = 0;
		int suffix=0;
		while ((num = in3.read(buf, off*bufsize*total_length, bufsize*total_length))> 0){
			
			Comparable [] A = new Comparable [num/total_length];
			int [] B = new int[num/total_length];
			if(s1[index_sort].equals("float")){
				//number
				for (int i =0;i<(num/total_length);i++){
					A[i] = readnum(buf, i*total_length+start_sort);
					B[i] = i;//System.out.print(B[i]);
				}
				mergeSort(A,B);				
			}else {
				//string
				for (int i =0;i<(num/total_length);i++){
					A[i] = readstr(buf, i*total_length+start_sort);
					B[i] = i;
					
					//System.out.print("A[i] = "+A[i]+"   B[i]="+B[i]+"\n");}
				}	
				mergeSort(A,B);
				//for(int i=0;i<A.length;i++)System.out.print(A[i]);
			}
		
		DataOutputStream out = new DataOutputStream(new FileOutputStream("T"+Integer.toString(suffix)));
    	for (int i =0;i<num/total_length;i++){
    		out.write(buf, B[i]*total_length, total_length);
    	}
			
    	out.close();
    	suffix++;
	}//end while
		
	/*Then next steps is to merge these binary file together, suffix is the number
		of binary files */
	//DataInputStream T0 = new DataInputStream(new FileInputStream("T0"));
	while(true){
	if(suffix==1){
		File f1 = new File("IR_Data2_"+inputRelation+"_"+s0[index_sort]+".bin");
		if(f1.exists())
			f1.delete();
		
		f1 = new File("T0"); 		
		f1.renameTo(new File("IR_Data2_"+inputRelation+"_"+s0[index_sort]+".bin")); //rename file
		break;
	}else{	
		RandomAccessFile Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");
		if(s1[index_sort].equals("float")){
			//number			
			Tin.skipBytes(start_sort);
			double current=Tin.readDouble();
			double next =-1;
			float pos1,pos2;
			int j= (int)Tin.length()/total_length-1;
			
			for(int i=0;i<j;){				
				//Tin2.close();
				RandomAccessFile Tin2 = new RandomAccessFile ("T0" ,"rw");
				
				pos1 = binarysearch_file(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, current);
				if (pos1 > Tin2.length()/total_length){
					Tin.seek(i*total_length);
					num = Tin.read(buf, 0, bufsize*total_length);
					Tin2.length();
					Tin2.seek(Tin2.length());
					Tin2.write(buf, 0, num);
					Tin2.close();
					Tin.close();
					File fff =new File("T"+Integer.toString(suffix-1));
					fff.delete();
					suffix--;
					break;
				}
				int block=0;
				while(true){
					num = Tin.skipBytes(total_length-8);
					if (num !=total_length-8){
						//read at the end of the file
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						Tin2.seek(0);
						
						int insert_p = ((int)(pos1)*total_length);
						//Tin2.read(buf, 0, insert_p); // insert_p may be exceed the buf size
						while( insert_p > bufsize*total_length){
							num = Tin2.read(buf,0, bufsize*total_length);
							TT.write(buf, 0, num);
							insert_p = insert_p - bufsize*total_length;
						}
						Tin2.read(buf, 0, insert_p);
						//so the above code replace the one line code
						TT.write(buf, 0, insert_p);
						Tin.seek(i*total_length);
						Tin.read(buf, 0, (block+1)*total_length);
						TT.write(buf, 0, (block+1)*total_length);
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						
						TT.close();
						Tin2.close();
						Tin.close();
						File file =new File("T0");
						file.delete();
						file = new File ("T"+Integer.toString(suffix-1));
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						suffix--;
						i = 10000; //big enough to end the for loop					
						
//						Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");						
//						i=0;
//						Tin.skipBytes(start_sort);
//						current = Tin.readDouble();
						break;	
					}// end if 					
					
					next = Tin.readDouble(); 
					block++;
					pos2 = binarysearch_file(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, next );
					if (pos1!=pos2){
						// read # = block, record into buf and insert into the T0 at pos1
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						
						if(pos1 <0){
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
							Tin2.seek(0);
							
						}else{
							int insert_p = ((int)(pos1)*total_length);
							Tin2.seek(0);
							while( insert_p > bufsize*total_length){
								num = Tin2.read(buf,0, bufsize*total_length);
								TT.write(buf, 0, num);
								insert_p = insert_p - bufsize*total_length;
							}
							Tin2.read(buf, 0, insert_p);
							//Tin2.read(buf, 0, insert_p);
							TT.write(buf, 0, insert_p);
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
						}
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						TT.close();
						Tin2.close();
						File file =new File("T0");
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						current =next;
						i = i+block;
						Tin.seek(i*total_length+start_sort+8);
						break;					
						
					}//end if 
				}// end while tru
				
			}// end for 
			
			Tin.close();
			File fff =new File("T"+Integer.toString(suffix-1));
			fff.delete();
			suffix--;
			//suffix--;
		}else{
			//string  String
			
			Tin.skipBytes(start_sort);
			byte [] current = new byte[20];
			Tin.read(current);
			byte [] next = new byte[20];
			float pos1,pos2;
			int j= (int)Tin.length()/total_length-1;
			 
			for(int i=0;i<j;){				
				RandomAccessFile Tin2 = new RandomAccessFile ("T0" ,"rw");
				
				pos1 = binarysearch_file_str(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, current);
				if (pos1 > Tin2.length()/total_length){
					Tin.seek(i*total_length);
					num = Tin.read(buf, 0, bufsize*total_length);
					Tin2.length();
					Tin2.seek(Tin2.length());
					Tin2.write(buf, 0, num);
					Tin2.close();
					Tin.close();
					File fff =new File("T"+Integer.toString(suffix-1));
					fff.delete();
					suffix--;
					break;
				}
				int block=0;
				while(true){
					num = Tin.skipBytes(total_length-20);
					if (num !=total_length-20){
						//read at the end of the file
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						Tin2.seek(0);
						
						int insert_p = ((int)(pos1)*total_length);
						//Tin2.read(buf, 0, insert_p); // insert_p may be exceed the buf size
						while( insert_p > bufsize*total_length){
							num = Tin2.read(buf,0, bufsize*total_length);
							TT.write(buf, 0, num);
							insert_p = insert_p - bufsize*total_length;
						}
						Tin2.read(buf, 0, insert_p);
						//so the above code replace the one line code
						TT.write(buf, 0, insert_p);
						Tin.seek(i*total_length);
						Tin.read(buf, 0, (block+1)*total_length);
						TT.write(buf, 0, (block+1)*total_length);
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						
						TT.close();
						Tin2.close();
						Tin.close();
						File file =new File("T0");
						file.delete();
						file = new File ("T"+Integer.toString(suffix-1));
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						suffix--;
						i = 10000; //big enough to end the for loop					
						
//						Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");						
//						i=0;
//						Tin.skipBytes(start_sort);
//						current = Tin.readDouble();
						break;	
					}// end if 					
					
					Tin.read(next); 
					block++;
					pos2 = binarysearch_file_str(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, next );
					if (pos1!=pos2){
						// read # = block, record into buf and insert into the T0 at pos1
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						
						if(pos1 <0){
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
							Tin2.seek(0);
							
						}else{
							int insert_p = ((int)(pos1)*total_length);
							Tin2.seek(0);
							while( insert_p > bufsize*total_length){
								num = Tin2.read(buf,0, bufsize*total_length);
								TT.write(buf, 0, num);
								insert_p = insert_p - bufsize*total_length;
							}
							Tin2.read(buf, 0, insert_p);
							//Tin2.read(buf, 0, insert_p);
							TT.write(buf, 0, insert_p);
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
						}
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						TT.close();
						Tin2.close();
						File file =new File("T0");
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						current =next;
						i = i+block;
						Tin.seek(i*total_length+start_sort+20);
						break;					
						
					}//end if 
				}// end while tru
				
			}// end for 
			Tin.close();		
			File fff =new File("T"+Integer.toString(suffix-1));
			fff.delete();	
			
			suffix--;
			
		}//end if string and number
		
	}//end if suffix==1
  }// end while
//	DataInputStream T0 = new DataInputStream(new FileInputStream("IR_Data2_test2_A.bin"));
//	T0.skipBytes(start_sort);
//	byte [] temp = new byte[20];
//	for (int i =0; i<20000;i++)
//	{
//		T0.read(temp);
//		System.out.print(temp.toString()+"\n");
//		T0.skipBytes(total_length-20);
//	}
//	
		
	}catch(FileNotFoundException fnfe)
  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
}


public static void indexFile(String R, String sortCol){
	try{
		
		BufferedReader in2 = new BufferedReader(new FileReader("IR_Head2_"+inputRelation+".bin"));
		
		String[] s0 = in2.readLine().split(",");
		String[] s1 = in2.readLine().split(",");
		String  s2 = in2.readLine();
		
		int index_sort=-1;
		int flag=0;
		int total_length=0;
		int start_sort=0;
		for (int i = 0; i<s0.length;i++){
			if (sortCol.equals(s0[i])){
				index_sort = i;
				flag++;				
			}
			if (s1[i].equals("string")){
				total_length +=20;				
			}else{
				total_length +=8;
			}
		}
		for (int i =0; i<index_sort;i++){
			if (s1[i].equals("string")){
				start_sort +=20;				
			}else{
				start_sort +=8;
			}
		}
		//if(flag!=1|s1[index_sort].equals("string")){
		//	System.out.print("wrong sort column, exit");
		//	System.exit(1);
		//}
		total_length +=2;	//add one position for marker
		start_sort+=2;   // add one position for marker
		int bufsize = 5000;
		byte[] buf = new byte[bufsize*total_length];
		DataInputStream in3 = new DataInputStream(new FileInputStream("IR_Data2_"+inputRelation+".bin"));
		int off = 0;
		int num = 0;
		int suffix=0;
		while ((num = in3.read(buf, off*bufsize*total_length, bufsize*total_length))> 0){
			
			Comparable [] A = new Comparable [num/total_length];
			int [] B = new int[num/total_length];
			if(s1[index_sort].equals("float")){
				//number
				for (int i =0;i<(num/total_length);i++){
					A[i] = readnum(buf, i*total_length+start_sort);
					B[i] = i;//System.out.print(B[i]);
				}
				mergeSort(A,B);				
			}else {
				//string
				for (int i =0;i<(num/total_length);i++){
					A[i] = readstr(buf, i*total_length+start_sort);
					B[i] = i;
					
					//System.out.print("A[i] = "+A[i]+"   B[i]="+B[i]+"\n");}
				}	
				mergeSort(A,B);
				//for(int i=0;i<A.length;i++)System.out.print(A[i]);
			}
		
		DataOutputStream out = new DataOutputStream(new FileOutputStream("T"+Integer.toString(suffix)));
    	//byte[]temp = new byte[20];
		for (int i =0;i<num/total_length;i++){
			if(s1[index_sort].equals("float")){
				//number
				out.writeChar('*');
				out.writeDouble((Double)A[i]);				
				out.writeInt(B[i]);
			}
  
    	}
			
    	out.close();
    	suffix++;
	}//end while
		
	/*Then next steps is to merge these binary file together, suffix is the number
		of binary files */
	//DataInputStream T0 = new DataInputStream(new FileInputStream("T0"));
	while(true){
	if(suffix==1){
		File f1 = new File("IR_Data2_"+inputRelation+"_"+s0[index_sort]+".ndx");
		if(f1.exists())
			f1.delete();
		
		f1 = new File("T0"); 		
		f1.renameTo(new File("IR_Data2_"+inputRelation+"_"+s0[index_sort]+".ndx")); //rename file
		break;
	}else{	
		RandomAccessFile Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");
		if(s1[index_sort].equals("float")){
			//number
			start_sort = 2;
			total_length = 14;
			Tin.skipBytes(start_sort);
			double current=Tin.readDouble();
			double next =-1;
			float pos1,pos2;
			int j= (int)Tin.length()/total_length-1;
			
			for(int i=0;i<j;){				
				//Tin2.close();
				RandomAccessFile Tin2 = new RandomAccessFile ("T0" ,"rw");
				
				pos1 = binarysearch_file(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, current);
				if (pos1 > Tin2.length()/total_length){
					Tin.seek(i*total_length);
					num = Tin.read(buf, 0, bufsize*total_length);
					Tin2.length();
					Tin2.seek(Tin2.length());
					Tin2.write(buf, 0, num);
					Tin2.close();
					Tin.close();
					File fff =new File("T"+Integer.toString(suffix-1));
					fff.delete();
					suffix--;
					break;
				}
				int block=0;
				while(true){
					num = Tin.skipBytes(total_length-8);
					if (num !=total_length-8){
						//read at the end of the file
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						Tin2.seek(0);
						
						int insert_p = ((int)(pos1)*total_length);
						//Tin2.read(buf, 0, insert_p); // insert_p may be exceed the buf size
						while( insert_p > bufsize*total_length){
							num = Tin2.read(buf,0, bufsize*total_length);
							TT.write(buf, 0, num);
							insert_p = insert_p - bufsize*total_length;
						}
						Tin2.read(buf, 0, insert_p);
						//so the above code replace the one line code
						TT.write(buf, 0, insert_p);
						Tin.seek(i*total_length);
						Tin.read(buf, 0, (block+1)*total_length);
						TT.write(buf, 0, (block+1)*total_length);
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						
						TT.close();
						Tin2.close();
						Tin.close();
						File file =new File("T0");
						file.delete();
						file = new File ("T"+Integer.toString(suffix-1));
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						suffix--;
						i = 10000; //big enough to end the for loop					
						
//						Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");						
//						i=0;
//						Tin.skipBytes(start_sort);
//						current = Tin.readDouble();
						break;	
					}// end if 					
					
					next = Tin.readDouble(); 
					block++;
					pos2 = binarysearch_file(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, next );
					if (pos1!=pos2){
						// read # = block, record into buf and insert into the T0 at pos1
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						
						if(pos1 <0){
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
							Tin2.seek(0);
							
						}else{
							int insert_p = ((int)(pos1)*total_length);
							Tin2.seek(0);
							while( insert_p > bufsize*total_length){
								num = Tin2.read(buf,0, bufsize*total_length);
								TT.write(buf, 0, num);
								insert_p = insert_p - bufsize*total_length;
							}
							Tin2.read(buf, 0, insert_p);
							//Tin2.read(buf, 0, insert_p);
							TT.write(buf, 0, insert_p);
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
						}
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						TT.close();
						Tin2.close();
						File file =new File("T0");
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						current =next;
						i = i+block;
						Tin.seek(i*total_length+start_sort+8);
						break;					
						
					}//end if 
				}// end while tru
				
			}// end for 
			
			Tin.close();
			File fff =new File("T"+Integer.toString(suffix-1));
			fff.delete();
			suffix--;
			//suffix--;
		}else{
			//string  String
			
			Tin.skipBytes(start_sort);
			byte [] current = new byte[20];
			Tin.read(current);
			byte [] next = new byte[20];
			float pos1,pos2;
			int j= (int)Tin.length()/total_length-1;
			 
			for(int i=0;i<j;){				
				RandomAccessFile Tin2 = new RandomAccessFile ("T0" ,"rw");
				
				pos1 = binarysearch_file_str(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, current);
				if (pos1 > Tin2.length()/total_length){
					Tin.seek(i*total_length);
					num = Tin.read(buf, 0, bufsize*total_length);
					Tin2.length();
					Tin2.seek(Tin2.length());
					Tin2.write(buf, 0, num);
					Tin2.close();
					Tin.close();
					File fff =new File("T"+Integer.toString(suffix-1));
					fff.delete();
					suffix--;
					break;
				}
				int block=0;
				while(true){
					num = Tin.skipBytes(total_length-20);
					if (num !=total_length-20){
						//read at the end of the file
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						Tin2.seek(0);
						
						int insert_p = ((int)(pos1)*total_length);
						//Tin2.read(buf, 0, insert_p); // insert_p may be exceed the buf size
						while( insert_p > bufsize*total_length){
							num = Tin2.read(buf,0, bufsize*total_length);
							TT.write(buf, 0, num);
							insert_p = insert_p - bufsize*total_length;
						}
						Tin2.read(buf, 0, insert_p);
						//so the above code replace the one line code
						TT.write(buf, 0, insert_p);
						Tin.seek(i*total_length);
						Tin.read(buf, 0, (block+1)*total_length);
						TT.write(buf, 0, (block+1)*total_length);
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						
						TT.close();
						Tin2.close();
						Tin.close();
						File file =new File("T0");
						file.delete();
						file = new File ("T"+Integer.toString(suffix-1));
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						suffix--;
						i = 10000; //big enough to end the for loop					
						
//						Tin = new RandomAccessFile ("T"+Integer.toString(suffix-1), "r");						
//						i=0;
//						Tin.skipBytes(start_sort);
//						current = Tin.readDouble();
						break;	
					}// end if 					
					
					Tin.read(next); 
					block++;
					pos2 = binarysearch_file_str(Tin2,0,(int)Tin2.length()/total_length,total_length,start_sort, next );
					if (pos1!=pos2){
						// read # = block, record into buf and insert into the T0 at pos1
						DataOutputStream TT = new DataOutputStream(new FileOutputStream("TT"));
						
						if(pos1 <0){
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
							Tin2.seek(0);
							
						}else{
							int insert_p = ((int)(pos1)*total_length);
							Tin2.seek(0);
							while( insert_p > bufsize*total_length){
								num = Tin2.read(buf,0, bufsize*total_length);
								TT.write(buf, 0, num);
								insert_p = insert_p - bufsize*total_length;
							}
							Tin2.read(buf, 0, insert_p);
							//Tin2.read(buf, 0, insert_p);
							TT.write(buf, 0, insert_p);
							Tin.seek(i*total_length);
							Tin.read(buf, 0, block*total_length);
							TT.write(buf, 0, block*total_length);
						}
						while( (num = Tin2.read(buf,0, bufsize*total_length)) >0){
							TT.write(buf, 0, num);
						}
						TT.close();
						Tin2.close();
						File file =new File("T0");
						file.delete();
						file = new File("TT");
						file.renameTo(new File("T0"));
						current =next;
						i = i+block;
						Tin.seek(i*total_length+start_sort+20);
						break;					
						
					}//end if 
				}// end while tru
				
			}// end for 
			Tin.close();		
			File fff =new File("T"+Integer.toString(suffix-1));
			fff.delete();	
			
			suffix--;
			
		}//end if string and number
		
	}//end if suffix==1
  }// end while
//	DataInputStream T0 = new DataInputStream(new FileInputStream("IR_Data2_test2_A.bin"));
//	T0.skipBytes(start_sort);
//	byte [] temp = new byte[20];
//	for (int i =0; i<20000;i++)
//	{
//		T0.read(temp);
//		System.out.print(temp.toString()+"\n");
//		T0.skipBytes(total_length-20);
//	}
//	
		
	}catch(FileNotFoundException fnfe)
  	{
		  System.err.println(fnfe);
		  }
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
}

public static double readnum(byte[] buf, int pos){
	
	byte[] b = new byte[8];
	for( int i =0; i<8;i++){
		b[i] = buf[pos+i];
	}
	ByteBuffer bb= ByteBuffer.wrap(b);
	double d= bb.getDouble();
	return d;	
}

public static String readstr(byte[] buf, int pos){
	
	byte[] b = new byte[20];
	for( int i =0; i<20;i++){
		b[i] = buf[pos+i];
	}
	//ByteBuffer bb= ByteBuffer.wrap(b);
	//double d= bb.getDouble();
	String st = new String(b);
	return st;	
}
public static void mergeSort( Comparable [ ] a, int [] b ) {
    Comparable [ ] tmpArray = new Comparable[ a.length ];
    int [] tmpint = new int [b.length];
    mergeSort( a, tmpArray,b,tmpint, 0, a.length - 1 );
}

private static void mergeSort( Comparable [ ] a, Comparable [ ] tmpArray,
        int[] b,int [] tmpint, int left, int right ) {
    if( left < right ) {
        int center = ( left + right ) / 2;
        mergeSort( a, tmpArray,b,tmpint, left, center );
        mergeSort( a, tmpArray,b,tmpint, center + 1, right );
        merge( a, tmpArray,b,tmpint, left, center + 1, right );
    }
}

private static void merge( Comparable [ ] a, Comparable [ ] tmpArray,
        int[] b, int[] tmpint, int leftPos, int rightPos, int rightEnd ) {
    int leftEnd = rightPos - 1;
    int tmpPos = leftPos;
    int numElements = rightEnd - leftPos + 1;
    
    // Main loop
    while( leftPos <= leftEnd && rightPos <= rightEnd )
        if( a[ leftPos ].compareTo( a[ rightPos ] ) <= 0 )
        {   tmpArray[ tmpPos ] = a[ leftPos ];
            tmpint [tmpPos ] = b[leftPos ];
            tmpPos++; leftPos++;}
        else
        { tmpArray[ tmpPos ] = a[ rightPos ];
          tmpint[tmpPos] = b[ rightPos ];
        tmpPos++;rightPos++;}
    
    while( leftPos <= leftEnd )    // Copy rest of first half
    { tmpArray[ tmpPos ] = a[ leftPos ];
      tmpint[ tmpPos] = b [ leftPos];
      tmpPos++; leftPos++;
    }
    
    while( rightPos <= rightEnd )  // Copy rest of right half
        {tmpArray[ tmpPos ] = a[ rightPos ];
         tmpint [tmpPos ] = b[rightPos];
         tmpPos++; rightPos++;
        }
    
    // Copy tmpArray back
    for( int i = 0; i < numElements; i++, rightEnd-- )
        {a[ rightEnd ] = tmpArray[ rightEnd ];
         b[rightEnd ] = tmpint [ rightEnd];
        }
}

private static void readBinary(int total_length)throws IOException{
	RandomAccessFile in = new RandomAccessFile ("T1", "r");
	for(int i=0;i<1000;i++)
		{double g = in.readDouble();
	     i=i+8;
	System.out.print(g+"\n");}	
}

public static float binarysearch_file(RandomAccessFile Tin2, int i,int j,int total_length, int start_sort,double f){
	try{
	float index =0.0f;
	int k =(i+j)/2;
	
	if ( i >= j){
		if(i!=0){
		Tin2.seek((i-1)*total_length+start_sort);
		}else{
			Tin2.seek(0+start_sort);
		}
		if (Tin2.readDouble() >f){
			return i-0.5f;
		}	else{
			return i+0.5f;
		}
	}
	if(k==0){
		Tin2.seek(0);
	}else{
	Tin2.seek((k-1)*total_length+start_sort);
	}
	if (Tin2.readDouble()==f){
		return k;
	}

	if(k==0){
		Tin2.seek(0);
	}else{
	Tin2.seek((k-1)*total_length+start_sort);
	}	
    if (Tin2.readDouble() >f){
		index = binarysearch_file(Tin2,i,k-1,total_length, start_sort,f);
	}else{
		index = binarysearch_file(Tin2,k+1,j,total_length,start_sort, f);
	}
    return index;
	}
	catch(IOException ioe)
		  {
		  System.err.println(ioe);
		  }
	return -1.f;
	
}
public static float binarysearch_file_str(RandomAccessFile Tin2, int i,int j,int total_length, int start_sort,byte[] f){
	try{
	float index =0.0f;
	int k =(i+j)/2;
	byte [] temp =new byte[20]; 
	
	if ( i >= j){
		if(i!=0){
		Tin2.seek((i-1)*total_length+start_sort);
		}else{
			Tin2.seek(0+start_sort);
		}
		Tin2.read(temp);
		if (temp.toString().compareTo(f.toString())>0){
			return i-0.5f;
		}	else{
			return i+0.5f;
		}
	}
	if(k==0){
		Tin2.seek(0);
	}else{
	Tin2.seek((k-1)*total_length+start_sort);
	}
	Tin2.read(temp);
	if (temp.toString().equals(f.toString())){
		return k;
	}

	if(k==0){
		Tin2.seek(0);
	}else{
	Tin2.seek((k-1)*total_length+start_sort);
	}
	Tin2.read(temp);
    if (temp.toString().compareTo(f.toString()) >0){
		index = binarysearch_file_str(Tin2,i,k-1,total_length, start_sort,f);
	}else{
		index = binarysearch_file_str(Tin2,k+1,j,total_length,start_sort, f);
	}
    return index;
	}
	catch(IOException ioe)
		  {
		  System.err.println("binsear"+ioe);
		  }
	return -1.f;
	
}

}